using System;
using System.IO;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace SurveyImporter
{
	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class frmMain : System.Windows.Forms.Form
	{
		#region Menu members
		private System.Windows.Forms.MainMenu mainMenu1;
		private System.Windows.Forms.MenuItem miFile;
		private System.Windows.Forms.MenuItem miFile_Open;
		private System.Windows.Forms.MenuItem miFile_Exit;
		private System.Windows.Forms.MenuItem miFile_Save;
		private System.Windows.Forms.StatusBar statusBar1;
		private System.Windows.Forms.MenuItem miOptions;
		private System.Windows.Forms.MenuItem miOptions_LockGrid;
		#endregion

        private System.Windows.Forms.DataGrid dgExcelSheet;
		
		private string m_strExcelFile;
		private OleDbConnection m_conn;
		private string[] m_worksheetnames; 
		private string m_connStr;
		private DataSet m_ds;
		public int m_languageID;

		private OpenFileDialog openFileDialog1;
        private IContainer components;

		public frmMain()
		{
			InitializeComponent();			
		}

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
            this.components = new System.ComponentModel.Container();
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(frmMain));
            this.mainMenu1 = new System.Windows.Forms.MainMenu(this.components);
            this.miFile = new System.Windows.Forms.MenuItem();
            this.miFile_Open = new System.Windows.Forms.MenuItem();
            this.miFile_Save = new System.Windows.Forms.MenuItem();
            this.miFile_Exit = new System.Windows.Forms.MenuItem();
            this.miOptions = new System.Windows.Forms.MenuItem();
            this.miOptions_LockGrid = new System.Windows.Forms.MenuItem();
            this.dgExcelSheet = new System.Windows.Forms.DataGrid();
            this.statusBar1 = new System.Windows.Forms.StatusBar();
            this.openFileDialog1 = new System.Windows.Forms.OpenFileDialog();
            ((System.ComponentModel.ISupportInitialize)(this.dgExcelSheet)).BeginInit();
            this.SuspendLayout();
            // 
            // mainMenu1
            // 
            this.mainMenu1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
            this.miFile,
            this.miOptions});
            // 
            // miFile
            // 
            this.miFile.Index = 0;
            this.miFile.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
            this.miFile_Open,
            this.miFile_Save,
            this.miFile_Exit});
            this.miFile.Text = "File";
            // 
            // miFile_Open
            // 
            this.miFile_Open.Index = 0;
            this.miFile_Open.Shortcut = System.Windows.Forms.Shortcut.CtrlO;
            this.miFile_Open.Text = "Open";
            this.miFile_Open.Click += new System.EventHandler(this.miFile_Open_Click);
            // 
            // miFile_Save
            // 
            this.miFile_Save.Enabled = false;
            this.miFile_Save.Index = 1;
            this.miFile_Save.Text = "Save";
            this.miFile_Save.Click += new System.EventHandler(this.miFile_Save_Click);
            // 
            // miFile_Exit
            // 
            this.miFile_Exit.Index = 2;
            this.miFile_Exit.Text = "Exit";
            this.miFile_Exit.Click += new System.EventHandler(this.miFile_Exit_Click);
            // 
            // miOptions
            // 
            this.miOptions.Index = 1;
            this.miOptions.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
            this.miOptions_LockGrid});
            this.miOptions.Text = "Options";
            // 
            // miOptions_LockGrid
            // 
            this.miOptions_LockGrid.Checked = true;
            this.miOptions_LockGrid.Index = 0;
            this.miOptions_LockGrid.Text = "Lock grid";
            this.miOptions_LockGrid.Click += new System.EventHandler(this.miOptions_LockGrid_Click);
            // 
            // dgExcelSheet
            // 
            this.dgExcelSheet.AllowNavigation = false;
            this.dgExcelSheet.CaptionVisible = false;
            this.dgExcelSheet.DataMember = "";
            this.dgExcelSheet.HeaderForeColor = System.Drawing.SystemColors.ControlText;
            this.dgExcelSheet.Location = new System.Drawing.Point(0, 0);
            this.dgExcelSheet.Name = "dgExcelSheet";
            this.dgExcelSheet.ReadOnly = true;
            this.dgExcelSheet.Size = new System.Drawing.Size(792, 553);
            this.dgExcelSheet.TabIndex = 0;
            // 
            // statusBar1
            // 
            this.statusBar1.Location = new System.Drawing.Point(0, 531);
            this.statusBar1.Name = "statusBar1";
            this.statusBar1.Size = new System.Drawing.Size(792, 22);
            this.statusBar1.TabIndex = 1;
            this.statusBar1.Text = "statusBar1";
            // 
            // openFileDialog1
            // 
            this.openFileDialog1.FileName = "Excel files|*.xls";
            // 
            // frmMain
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(792, 553);
            this.Controls.Add(this.statusBar1);
            this.Controls.Add(this.dgExcelSheet);
            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
            this.Menu = this.mainMenu1;
            this.MinimumSize = new System.Drawing.Size(200, 150);
            this.Name = "frmMain";
            this.Text = "Survey Import";
            this.Load += new System.EventHandler(this.frmMain_Load);
            this.Closing += new System.ComponentModel.CancelEventHandler(this.Form1_Closing);
            this.Resize += new System.EventHandler(this.frmMain_Resize);
            ((System.ComponentModel.ISupportInitialize)(this.dgExcelSheet)).EndInit();
            this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new frmMain());
		}

		#region Menu Events
		private void miFile_Open_Click(object sender, System.EventArgs e)
		{
			statusBar1.Text = "Opening file...";
			openFileDialog1.FileName = "*.xls";
			
			if(openFileDialog1.ShowDialog()!=DialogResult.Cancel)
			{
               
                m_strExcelFile = openFileDialog1.FileName;
                
                FileStream tmpExcelFile = null;
				bool fileInUse = false;

				//check file isn't already open
				try
				{
					tmpExcelFile = File.Open(m_strExcelFile,FileMode.Open,FileAccess.Read,FileShare.None);
					tmpExcelFile.Close();
				}
				catch
				{
					fileInUse = true;
					if(MessageBox.Show("Exclusive access to the file is needed.\n\nPlease close it and re-try.","File already open",MessageBoxButtons.RetryCancel,MessageBoxIcon.Error)==DialogResult.Retry)
					{
						miFile_Open_Click(sender,e);
						return;
					}						
				}
				finally
				{
					if(tmpExcelFile!=null)
						tmpExcelFile.Close();
				}
				
				if(!fileInUse) //it's ok to go ahead
				{
					m_connStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strExcelFile + @";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""; 					
					GetWorkSheetNames();
					GetWorkSheets();					
				}
				else
					statusBar1.Text = "Error opening the file.";
			}
			else
				statusBar1.Text = "Ready... Please open a document to process.";
		}
        
		private void miFile_Exit_Click(object sender, System.EventArgs e)
		{
			this.Close();
		}
		#endregion

		#region Opening Excel Sheet
		private string[] GetWorkSheetNames() 
		{ 
			m_conn = new OleDbConnection(m_connStr); 
			
			string _error = string.Empty;
            
			try 
			{ 
				m_conn.Open(); 

				// Get all of the Table names from the Excel workbook 
				DataTable dt = m_conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 
				m_worksheetnames = new string[dt.Rows.Count]; 

				//Add the Table name to the string array. 
				for(int i=0; i < dt.Rows.Count; i++) 
				{ 
					m_worksheetnames[i] = (string)dt.Rows[i]["TABLE_NAME"]; 
					//MessageBox.Show(m_worksheetnames[i]);
				} 
			} 
			catch(Exception e) 
			{ 
				_error += e.Message + "\n\n"; 
			} 
			finally 
			{ 
				m_conn.Close(); 
			} 

			if(_error!=string.Empty)
				MessageBox.Show(_error);

			return m_worksheetnames; 
		}

		private void GetWorkSheets() 
		{ 	
			string _error = string.Empty;

			try 
			{ 
				// for each sheet, right now only one, If the worksheet name exists... 
				/*
				for(int i=0; i < m_worksheetnames.Length; i++) 
				{ 
					// Open the connection to the Excel document, and select the data from the first worksheet 
					m_conn = new OleDbConnection(m_connStr); 
					m_conn.Open(); 
					OleDbDataAdapter da = new OleDbDataAdapter(@"SELECT * FROM [" + _worksheetnames[i] + "]", m_conn); 
					da.Fill(_ds, "Table" + i); 
				} */
				
				int wsNum = 0;
				if(m_worksheetnames != null && m_worksheetnames[wsNum] != string.Empty)
				{
					//MessageBox.Show(m_worksheetnames[wsNum]);

					statusBar1.Text = "Loading Excel data...";
					m_conn = new OleDbConnection(m_connStr); 
					m_conn.Open(); 
					OleDbDataAdapter da = new OleDbDataAdapter(@"SELECT * FROM [" + m_worksheetnames[wsNum] + "]", m_conn); 
					m_ds = new DataSet("ExcelData");
					da.Fill(m_ds); 
										
					RemoveNullRows();	
										
					if(m_ds!=null)
					{
                        if(m_ds.Tables[0].Columns.Count > 0)
                        {
                            dgExcelSheet.DataSource = m_ds.Tables[0];
                            miFile_Save.Enabled = true;
                            statusBar1.Text = "Ready to import.";
                        }
                        else
                        {
                            m_ds.Clear();
                            m_ds.Dispose();
                            m_strExcelFile = string.Empty;

                            MessageBox.Show("The excel worksheet is not in the correct format.\nThis file cannot be used.", "Excel format error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        }		

                        statusBar1.Text = "Excel data loaded.";
				
					}
					else
						MessageBox.Show("DS is null!");
				}
			} 
			catch(Exception e) 
			{ 
				_error += e.Message + "\n\n"; 
			} 
			finally 
			{ 
				m_conn.Close(); 
			} 

			if(_error!=string.Empty)
				MessageBox.Show(_error);
		} 

		#endregion

		#region Clean Up DataGrid
		private void RemoveNullRows()
		{
			if(m_ds!=null)
			{
				statusBar1.Text = "Removing empty rows...";
				for(int i = 0; i < m_ds.Tables[0].Rows.Count; i++)
				{
					bool delete = true;

					for(int j = 0; j < m_ds.Tables[0].Columns.Count; j++)
					{
						if(!Convert.IsDBNull(m_ds.Tables[0].Rows[i][j]))
						{
							delete = false;
							break;
						}
					}

					if(delete)
					{
						m_ds.Tables[0].Rows.Remove(m_ds.Tables[0].Rows[i]);
						i = 0; //need to start over we might be jumping over null rows.
					}
				}			
			}
		}		
		#endregion

		private void Form1_Closing(object sender, System.ComponentModel.CancelEventArgs e)
		{
			//Do some cleanup
			if(m_ds!=null)
			{
				m_ds.Clear();
				m_ds.Dispose();
			}
		}

		private void frmMain_Load(object sender, System.EventArgs e)
		{
			//dgExcelSheet.PreferredColumnWidth = (Width/4)-16;		        
			statusBar1.Text = "Ready... Please open a document to process.";
		}

		private void miOptions_LockGrid_Click(object sender, System.EventArgs e)
		{
			miOptions_LockGrid.Checked = !miOptions_LockGrid.Checked;
			dgExcelSheet.ReadOnly = miOptions_LockGrid.Checked;
		}

        private void frmMain_Resize(object sender, EventArgs e)
        {
            dgExcelSheet.Width = this.Width - 8;
            dgExcelSheet.Height = this.Height - 70;
        }

        private void processData()
        {
            // create a writer and open the file
            TextWriter tw = new StreamWriter("courses.xml");
                       
            tw.WriteLine("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>");
            tw.WriteLine("<classes>");

            DataRow dr;
            string prevCourse = string.Empty;
            string prevSection = string.Empty;
            string prevTutSection = string.Empty;
            string prevLabSection = string.Empty;
            string prevTime = string.Empty;

            bool firstRecord = true;
            bool firstLecture = true;
            bool tutorialOpen = false;
            bool labOpen = false;
            
            for (int i = 0; i < m_ds.Tables[0].Rows.Count; i++)
            {                
                dr = m_ds.Tables[0].Rows[i];

                if (prevCourse != dr[1].ToString() + dr[2].ToString())
                {
                    prevCourse = dr[1].ToString() + dr[2].ToString();

                    if (firstRecord)                                           
                        firstRecord = false;
                    else
                    {
                        if (labOpen)
                        {
                            labOpen = false;
                            tw.WriteLine("</lab>");
                        }
                        
                        if (tutorialOpen)
                        {
                            tutorialOpen = false;
                            tw.WriteLine("</tutorial>");
                        }                     

                        tw.WriteLine("</lecture>");
                        tw.WriteLine("</sections>");
                        tw.WriteLine("</class>");
                    }
                    
                    tw.WriteLine("<class name=\"" + dr[1].ToString() + "\" number=\"" + dr[2].ToString() + "\" description=\"none available right now\" credits=\"" + dr[11].ToString() + "\">");                    
                    tw.WriteLine("<sections>");

                    prevSection = string.Empty; //clear it so we output a new one
                    firstLecture = true; //reset a new course so a new series of lectures                    
                }

                //output the course sections
                if (prevSection != dr[3].ToString())
                {
                    prevSection = dr[3].ToString();

                    if (labOpen)
                    {
                        labOpen = false;
                        tw.WriteLine("</lab>");
                    }
                    
                    if (tutorialOpen)
                    {
                        tutorialOpen = false;
                        tw.WriteLine("</tutorial>");
                    }                   
                    
                    if (firstLecture)
                        firstLecture = false;
                    else
                        tw.WriteLine("</lecture>");
                    
                    tw.WriteLine("<lecture name=\"" + dr[3].ToString() + "\" type=\"" + dr[6].ToString() + "\" professor=\"TBD\" location=\"Unknown\" semester=\"" + dr[0].ToString() + "\">");
                    
                    prevTime = string.Empty; //clear it so we output a new one
                }

                //Output for tutorials
                if (prevTutSection != dr[4].ToString() && dr[6].ToString().ToUpper() == "T")                
                {
                    if (labOpen)
                    {
                        labOpen = false;
                        tw.WriteLine("</lab>");
                    }
                    
                    if (tutorialOpen)
                    {
                        tutorialOpen = false;
                        tw.WriteLine("</tutorial>");
                    }               
                    
                    prevTutSection = dr[4].ToString();
                    tw.WriteLine("<tutorial name=\"" + dr[4].ToString() + "\" type=\"" + dr[6].ToString() + "\" professor=\"TBD\" location=\"Unknown\" semester=\"" + dr[0].ToString() + "\">");
                    
                    prevTime = string.Empty;
                    tutorialOpen = true;                    
                }

                //Output for labs
                if (prevLabSection != dr[5].ToString() && dr[6].ToString().ToUpper() == "X")
                {
                    if (labOpen)
                    {
                        labOpen = false;                        
                        tw.WriteLine("</lab>");
                    }

                    prevLabSection = dr[5].ToString();
                    tw.WriteLine("<lab name=\"" + dr[5].ToString() + "\" type=\"" + dr[6].ToString() + "\" professor=\"TBD\" location=\"Unknown\" semester=\"" + dr[0].ToString() + "\">");
                   
                    prevTime = string.Empty;
                    labOpen = true;                    
                }

                if (prevTime != dr[9].ToString().Replace("-", ""))
                {
                    prevTime = dr[9].ToString().Replace("-", "");
                    tw.WriteLine("<time day=\"" + prevTime + "\" begin=\"" + dr[7].ToString() + "\" end=\"" + dr[8].ToString() + "\" />");
                }

            }

            if (labOpen)
            {
                labOpen = false;
                tw.WriteLine("</lab>");
            }

            if (tutorialOpen)
            {
                tutorialOpen = false;
                tw.WriteLine("</tutorial>");
            }          

            tw.WriteLine("</lecture>");
            tw.WriteLine("</sections>");
            tw.WriteLine("</class>");

            tw.WriteLine("</classes>");
            // close the stream
            tw.Close();
        }

        private void miFile_Save_Click(object sender, EventArgs e)
        {
            Cursor = Cursors.WaitCursor;
            miFile.Enabled = false;

            processData();

            Cursor = Cursors.Arrow;
            miFile.Enabled = true;
            MessageBox.Show("Course XML file generation complete", "Excel Importer", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
	}
}
